package com.luli.entity.poi;

import com.luli.Utils.FileUtil;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 陆离
 * 2022/5/6 8:54:22
 */
public class ExportExcel implements Serializable {
    public static final int ROW_ACCESS_WINDOW_SIZE = 10000;
    private static final long serialVersionUID = -1715237191325535025L;
    private static final Logger logger = LoggerFactory.getLogger(ExportExcel.class);
    private String curTitle;
    private String fileName;
    private String[] curRowName;
    private String[] curRowNameNext;
    //合并单元格区域
    private List<CellRangeAddressLocal> mergeRanges;

    private WorkBookLocal localWb;
    private List<Object[]> curDataList = new ArrayList<Object[]>();
    private SXSSFWorkbook sfwb = null;

    public ExportExcel(WorkBookLocal localWb) {
        this.localWb = localWb;
    }

    public void export(HttpServletResponse response) {
        if (!generateWorkBook()) {
            return;
        }
        writeWorkBook(response);
    }

    public byte[] getWorkBookStream() {
        if (!generateWorkBook()) {
            return null;
        }
        if (sfwb == null) {
            return null;
        }
        ByteArrayOutputStream bos = null;
        try {
            bos = new ByteArrayOutputStream();
            sfwb.write(bos);
            return bos.toByteArray();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (bos != null) {
                try {
                    bos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public String getFileName(){
        return this.fileName;
    }

    /**
     * 直接输出文件 到指定目录
     * @param fileDirPath
     * @return
     * @throws UnsupportedEncodingException
     */
    public File export(String fileDirPath) throws UnsupportedEncodingException {
        if (!generateWorkBook()) {
            return null;
        }
        if (sfwb == null) {
            return null;
        }
        if (StringUtils.isBlank(fileDirPath)) {
            logger.error("Excel template path is empty！");
            throw new BizException("临时文件路径为空");
        }
        fileDirPath = checkTemplatePath(fileDirPath);
        File dirPath = new File(URLDecoder.decode(fileDirPath, "UTF-8"));
        // 文件夹是否存在，不存在创建
        if (!dirPath.exists()) {
            dirPath.mkdirs();
        }
        //删除超过7天的数据
        FileUtil.deleteFiles(fileDirPath, 7);
        File file = new File(dirPath, URLDecoder.decode(this.fileName+ ".xlsx", "UTF-8"));
        if (file.exists()) {
            FileUtils.deleteQuietly(file);
        }
        FileOutputStream fos = null;
        ByteArrayOutputStream bos = null;
        BufferedInputStream bis = null;
        try {
            fos = new FileOutputStream(file);
            bos=new ByteArrayOutputStream();
            sfwb.write(bos);
            bis = new BufferedInputStream(new ByteArrayInputStream(bos.toByteArray()));
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                fos.write(buff, 0, bytesRead);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            closeOutputStrem(fos);
            closeOutputStrem(bos);
            closeInputStream(bis);
        }
        return file;
    }

    public void closeInputStream(InputStream is) {
        if (is != null) {
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public void closeOutputStrem(OutputStream os) {
        if (os != null) {
            try {
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     *
     * @param filePath
     * @return
     */
    private String checkTemplatePath(String filePath) {
        return filePath.replace("+", "%2B");
    }

    private void writeWorkBook(HttpServletResponse response) throws BizException {
        ServletOutputStream out = null;
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        ByteArrayOutputStream os = null;
        ByteArrayInputStream is = null;
        try {
            if (sfwb != null) {
                response.reset();
                response.setHeader(
                        "Content-Disposition",
                        "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                out = response.getOutputStream();
                os = new ByteArrayOutputStream();
                sfwb.write(os);
                is = new ByteArrayInputStream(os.toByteArray());
                bis = new BufferedInputStream(is);
                bos = new BufferedOutputStream(out);
                byte[] buff = new byte[2048];
                int bytesRead;
                // Simple read/write loop.
                while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                    bos.write(buff, 0, bytesRead);
                }
            }
        } catch (BizException e) {
            logger.error("BizException:{}", e);
            throw new BizException(e.getErrorCode(), e.getErrorMsg());
        } catch (Exception e) {
            logger.error("BizException:{}", e);
            throw new BizException(BizException.ERROR_CODE, "报表生成失败！");
        } finally {
            closeOutputStrem(bos);
            closeInputStream(bis);
            closeOutputStrem(os);
        }
    }

    private boolean generateWorkBook() {
        if (localWb == null) {
            logger.warn("无内容输出");
            return false;
        }
        if (StringUtils.isBlank(localWb.getFileName())
                && CollectionUtils.isNotEmpty(localWb.getSheets())) {
            logger.warn("文件名不能为空");
            return false;
        }
        if (StringUtils.isBlank(localWb.getFileName())
                && CollectionUtils.isNotEmpty(localWb.getSheets())) {
            logger.warn("文件名不能为空");
            //        return;
            // 没有名字，设置默认文件名
            localWb.setFileName(
                    DateFormatUtils.format(new Date(), "yyyy年MM月dd日")
                            .concat("报表-")
                            .concat(String.valueOf(System.currentTimeMillis())));
        }
        //      SXSSFWorkbook sfwb = null;
        if (CollectionUtils.isNotEmpty(localWb.getSheets())) {
            sfwb = new SXSSFWorkbook(ROW_ACCESS_WINDOW_SIZE);
            for (int i = 0; i < localWb.getSheets().size(); i++) {
                SheetLocal localSheet = localWb.getSheets().get(i);
                // 设置本地sheet相关信息
                this.curTitle = localSheet.getTitle();
                this.curRowName = localSheet.getRowName();
                this.curRowNameNext = localSheet.getRowNameNext();
                this.curDataList = localSheet.getDataList();
                this.mergeRanges=localSheet.getMergeRanges();
                String sheetname =
                        StringUtils.isNotBlank(localSheet.getSheetName())
                                ? localSheet.getSheetName()
                                : localSheet.getTitle();
                if (StringUtils.isBlank(sheetname)) {
                    throw new BizException(BizException.ERROR_CODE, String.format("报表第%d个sheet名不能为空", i));
                }
                Sheet currentSheet = sfwb.createSheet(sheetname);

                // 分sheet打印
                write_sheet(sfwb, currentSheet);
            }
        }
        // 输出

        // 设置工作簿的名称
        this.fileName = localWb.getFileName();
        return true;
    }

    /**
     * 分sheet输出
     * @param sfwb
     * @param sheet
     */
    private void write_sheet(SXSSFWorkbook sfwb, Sheet sheet) {
        Row rowm = sheet.createRow(0);
        Cell cellTiltle = rowm.createCell(0);
        // 设置样式
        CellStyle columnTopStyle = this.getColumnTopStyle(sfwb);
        CellStyle style = this.getStyle(sfwb);
        // 第一行的标题信息
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (curRowName.length - 1)));
        cellTiltle.setCellStyle(columnTopStyle);
        cellTiltle.setCellValue(curTitle);
        // 标题列行
        int rowNumber = 2;
        int columnNum = curRowName.length;
        Row rowRowName = sheet.createRow(rowNumber);
        rowNumber++;
        for (int n = 0; n < columnNum; n++) {
            Cell cellRowName = rowRowName.createCell(n);
            cellRowName.setCellType(Cell.CELL_TYPE_STRING);
            RichTextString text = new XSSFRichTextString(curRowName[n]);
            cellRowName.setCellValue(text);
            cellRowName.setCellStyle(columnTopStyle);
            // resetColumnWidth(sheet, cellRowName);
        }


        // 附加标题单元格
        if (curRowNameNext != null && curRowNameNext.length > 0) {
            int columnNextNum = curRowNameNext.length;
            Row rowRowNameNext = sheet.createRow(rowNumber);
            rowNumber++;
            for (int n = 0; n < columnNextNum; n++) {
                Cell cellRowName = rowRowNameNext.createCell(n);
                cellRowName.setCellType(Cell.CELL_TYPE_STRING);
                if (curRowNameNext[n] == null) {
                    CellStyle columnCenterStyle = this.getColumnTopStyle(sfwb);
                    columnCenterStyle.setAlignment(CellStyle.ALIGN_CENTER);
                    columnCenterStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
                    sheet.addMergedRegion(new CellRangeAddress(rowNumber - 2, rowNumber - 1, n, n));
                    cellRowName.setCellStyle(columnCenterStyle);
                    continue;
                }
                RichTextString text = new XSSFRichTextString(curRowNameNext[n]);
                cellRowName.setCellValue(text);
                cellRowName.setCellStyle(columnTopStyle);
            }
        }
        // 合合并单元格
        if (CollectionUtils.isNotEmpty(mergeRanges)) {
            for (CellRangeAddressLocal mergeRange : mergeRanges) {
                CellStyle columnCenterStyle = this.getColumnTopStyle(sfwb);
                columnCenterStyle.setAlignment(CellStyle.ALIGN_CENTER);
                columnCenterStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
                sheet.addMergedRegion(
                        new CellRangeAddress(
                                mergeRange.getFirstRow(),
                                mergeRange.getLastRow(),
                                mergeRange.getFirstCol(),
                                mergeRange.getLastCol()));
                sheet
                        .getRow(mergeRange.getFirstRow())
                        .getCell(mergeRange.getFirstCol())
                        .setCellStyle(columnCenterStyle);
            }
        }

        // 内容起始行
        // int contentRowNumStart=rowNumber;
        // 输出值
        for (int i = 0; i < curDataList.size(); i++) {

            Object[] obj = curDataList.get(i);
            Row row = sheet.createRow(i + rowNumber);

            for (int j = 0; j < obj.length; j++) {
                Cell cell;
                if (!"".equals(obj[j]) && obj[j] != null) {
                    cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                    cell.setCellValue(obj[j].toString());
                }else{
                    cell = row.createCell(j);
                }
                cell.setCellStyle(style);
            }
        }
        // 调整宽度
        for (int colNum = 0; colNum < columnNum; colNum++) {
            sheet.autoSizeColumn(colNum, true);
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 1; rowNum < sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(colNum) != null) {
                    Cell currentCell = currentRow.getCell(colNum);
                    if (currentCell.getCellType() == Cell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue().trim().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            //      if (colNum == 1) {
            //        sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
            //      } else {
            // 最大限制256
            sheet.setColumnWidth(
                    colNum, (((columnWidth + 5) * 256) > 255 * 256) ? 255 * 256 : ((columnWidth + 5) * 256));
            //      }
        }
    }

    public CellStyle getColumnTopStyle(Workbook workbook) {
        // 设置字体
        Font font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体加粗
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        CellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(CellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(IndexedColors.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(CellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(IndexedColors.BLACK.index);
        // 设置右边框;
        style.setBorderRight(CellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(IndexedColors.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(CellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(IndexedColors.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(CellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        return style;
    }

    public void resetColumnWidth(Sheet sheet, Cell currentCell) {
        if (currentCell == null) {
            return;
        }
        int columnNum = currentCell.getColumnIndex();
        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            int length = currentCell.getStringCellValue().getBytes().length;
            if (columnWidth < length) {
                columnWidth = length;
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }

    public CellStyle getStyle(Workbook workbook) {
        // 设置字体
        Font font = workbook.createFont();
        // 设置字体大小
        // font.setFontHeightInPoints((short)10);
        // 字体加粗
        // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        CellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(CellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(IndexedColors.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(CellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(IndexedColors.BLACK.index);
        // 设置右边框;
        style.setBorderRight(CellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(IndexedColors.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(CellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(IndexedColors.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(CellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        return style;
    }
}

